-- 元数据质量报告表
CREATE TABLE DATA_METADATA_QUALITY_REPORT (
    -- 主键ID
    ID VARCHAR(64) NOT NULL,
    
    -- 采集任务ID
    COLLECTION_ID VARCHAR(64),
    
    -- 执行ID
    EXECUTION_ID VARCHAR(64),
    
    -- 版本号
    VERSION INTEGER,
    
    -- 表名
    TABLE_NAME VARCHAR(128),
    
    -- 总行数
    TOTAL_ROWS BIGINT,
    
    -- 违规数量
    VIOLATION_COUNT INTEGER,
    
    -- 违规详情（JSON格式）
    VIOLATION_DETAILS CLOB,
    
    -- 创建时间
    CREATE_TIME TIMESTAMP DEFAULT SYSDATE,
    
    -- 更新时间
    UPDATE_TIME TIMESTAMP DEFAULT SYSDATE,
    
    -- 是否删除（0-未删除，1-已删除）
    IS_DELETED INTEGER DEFAULT 0,
    
    -- 是否最新版本（0-否，1-是）
    IS_LATEST INTEGER DEFAULT 1,
    
    -- 设置主键约束
    CONSTRAINT PK_DATA_METADATA_QUALITY_REPORT PRIMARY KEY (ID)
);

-- 创建索引
CREATE INDEX IDX_QUALITY_REPORT_COLLECTION_ID ON DATA_METADATA_QUALITY_REPORT (COLLECTION_ID);
CREATE INDEX IDX_QUALITY_REPORT_EXECUTION_ID ON DATA_METADATA_QUALITY_REPORT (EXECUTION_ID);
CREATE INDEX IDX_QUALITY_REPORT_TABLE_NAME ON DATA_METADATA_QUALITY_REPORT (TABLE_NAME);
CREATE INDEX IDX_QUALITY_REPORT_VERSION ON DATA_METADATA_QUALITY_REPORT (VERSION);
CREATE INDEX IDX_QUALITY_REPORT_IS_LATEST ON DATA_METADATA_QUALITY_REPORT (IS_LATEST);
CREATE INDEX IDX_QUALITY_REPORT_IS_DELETED ON DATA_METADATA_QUALITY_REPORT (IS_DELETED);

-- 添加表约束
ALTER TABLE DATA_METADATA_QUALITY_REPORT ADD CONSTRAINT CK_QUALITY_REPORT_IS_DELETED CHECK (IS_DELETED IN (0, 1));
ALTER TABLE DATA_METADATA_QUALITY_REPORT ADD CONSTRAINT CK_QUALITY_REPORT_IS_LATEST CHECK (IS_LATEST IN (0, 1));

-- 创建序列（用于生成自增ID）
CREATE SEQUENCE SEQ_METADATA_QUALITY_REPORT_ID
    START WITH 1
    INCREMENT BY 1
    NOCACHE;

-- 创建触发器（自动生成ID）
CREATE OR REPLACE TRIGGER TRG_METADATA_QUALITY_REPORT_ID
BEFORE INSERT ON DATA_METADATA_QUALITY_REPORT
FOR EACH ROW
BEGIN
    IF :NEW.ID IS NULL THEN
        SELECT 'QR' || TO_CHAR(SYSDATE, 'YYYYMMDD') || LPAD(SEQ_METADATA_QUALITY_REPORT_ID.NEXTVAL, 6, '0')
        INTO :NEW.ID
        FROM DUAL;
    END IF;
END;
/

-- 创建触发器（自动更新更新时间）
CREATE OR REPLACE TRIGGER TRG_METADATA_QUALITY_REPORT_UPDATE
BEFORE UPDATE ON DATA_METADATA_QUALITY_REPORT
FOR EACH ROW
BEGIN
    :NEW.UPDATE_TIME := SYSDATE;
END;
/

-- 添加注释
COMMENT ON TABLE DATA_METADATA_QUALITY_REPORT IS '元数据质量报告表';
COMMENT ON COLUMN DATA_METADATA_QUALITY_REPORT.ID IS '主键ID';
COMMENT ON COLUMN DATA_METADATA_QUALITY_REPORT.COLLECTION_ID IS '采集任务ID';
COMMENT ON COLUMN DATA_METADATA_QUALITY_REPORT.EXECUTION_ID IS '执行ID';
COMMENT ON COLUMN DATA_METADATA_QUALITY_REPORT.VERSION IS '版本号';
COMMENT ON COLUMN DATA_METADATA_QUALITY_REPORT.TABLE_NAME IS '表名';
COMMENT ON COLUMN DATA_METADATA_QUALITY_REPORT.TOTAL_ROWS IS '总行数';
COMMENT ON COLUMN DATA_METADATA_QUALITY_REPORT.VIOLATION_COUNT IS '违规数量';
COMMENT ON COLUMN DATA_METADATA_QUALITY_REPORT.VIOLATION_DETAILS IS '违规详情（JSON格式）';
COMMENT ON COLUMN DATA_METADATA_QUALITY_REPORT.CREATE_TIME IS '创建时间';
COMMENT ON COLUMN DATA_METADATA_QUALITY_REPORT.UPDATE_TIME IS '更新时间';
COMMENT ON COLUMN DATA_METADATA_QUALITY_REPORT.IS_DELETED IS '是否删除（0-未删除，1-已删除）';
COMMENT ON COLUMN DATA_METADATA_QUALITY_REPORT.IS_LATEST IS '是否最新版本（0-否，1-是）';

-- 创建视图（查询最新版本的质量报告）
CREATE OR REPLACE VIEW V_METADATA_QUALITY_REPORT_LATEST AS
SELECT *
FROM DATA_METADATA_QUALITY_REPORT
WHERE IS_LATEST = 1 AND IS_DELETED = 0;

-- 创建存储过程（更新质量报告版本）
CREATE OR REPLACE PROCEDURE PROC_UPDATE_QUALITY_REPORT_VERSION(
    p_collection_id IN VARCHAR2,
    p_execution_id IN VARCHAR2
) AS
BEGIN
    -- 将所有旧版本标记为非最新
    UPDATE DATA_METADATA_QUALITY_REPORT
    SET IS_LATEST = 0
    WHERE COLLECTION_ID = p_collection_id
    AND EXECUTION_ID <> p_execution_id
    AND IS_LATEST = 1;
    
    COMMIT;
END;
/

-- 创建函数（获取最大版本号）
CREATE OR REPLACE FUNCTION FUNC_GET_QUALITY_REPORT_MAX_VERSION(
    p_collection_id IN VARCHAR2
) RETURN NUMBER AS
    v_max_version NUMBER;
BEGIN
    SELECT COALESCE(MAX(VERSION), 0)
    INTO v_max_version
    FROM DATA_METADATA_QUALITY_REPORT
    WHERE COLLECTION_ID = p_collection_id
    AND IS_DELETED = 0;
    
    RETURN v_max_version;
END;
/

-- 插入示例数据
INSERT INTO DATA_METADATA_QUALITY_REPORT (
    ID, COLLECTION_ID, EXECUTION_ID, VERSION, TABLE_NAME,
    TOTAL_ROWS, VIOLATION_COUNT, VIOLATION_DETAILS,
    CREATE_TIME, UPDATE_TIME, IS_DELETED, IS_LATEST
) VALUES (
    'QR20230101000001', 'COL20230101000001', 'EXE20230101000001', 1, 'CUSTOMER',
    1000, 5, '{"null_values": 2, "duplicate_values": 3, "details": [{"column": "email", "error": "null_value", "count": 2}, {"column": "customer_id", "error": "duplicate_value", "count": 3}]}',
    SYSDATE, SYSDATE, 0, 1
);

INSERT INTO DATA_METADATA_QUALITY_REPORT (
    ID, COLLECTION_ID, EXECUTION_ID, VERSION, TABLE_NAME,
    TOTAL_ROWS, VIOLATION_COUNT, VIOLATION_DETAILS,
    CREATE_TIME, UPDATE_TIME, IS_DELETED, IS_LATEST
) VALUES (
    'QR20230101000002', 'COL20230101000001', 'EXE20230101000001', 1, 'ORDER',
    500, 2, '{"null_values": 1, "invalid_date": 1, "details": [{"column": "customer_id", "error": "null_value", "count": 1}, {"column": "order_date", "error": "invalid_date", "count": 1}]}',
    SYSDATE, SYSDATE, 0, 1
); 